<?php
class m_classify extends Zmodel{
	function get_all_classifycategory(){
		$this->load->database();
		$sql="SELECT * FROM classify_categories ORDER BY classify_desc ASC";
		return $this->db->query($sql);
	}

	function isMyClassified($pro_id,$userid){
		$result=0;
		$this->load->database();
		$sql="SELECT product_name FROM product_ads WHERE product_id=".$pro_id." AND user_id=".$userid;
	    $query = $this->db->query($sql);
        $nb_rows = $query->num_rows();
        if ($nb_rows < 1) {
            return NULL;
        } else {
            return $query->row_array();
        }
		
		//$rst=$this->db->query($sql);
		//$result=(count($rst->result())>0)?1:0;
		//return $result;
	}
	function get_classify_product($classifydesc,$num, $offset){
		$this->load->database();
		$sqlstatement="SELECT p.product_id,
            		 p.product_name,
            		 p.product_desc,
            		 p.price,
            		 p.date_post,
            		 p.date_close,
            		 p.classify_id,
            		 c.classify_desc,
            		 p.user_id,
            		 u.user_login,
            		 p.file_path
			FROM product_ads AS p INNER JOIN classify_categories AS c ON p.classify_id = c.classify_id 
			INNER JOIN users AS u ON p.user_id=u.user_id 
		    WHERE c.classify_desc LIKE '%".pg_escape_string($classifydesc)."%'
		    ORDER BY p.date_post DESC
          		  	    LIMIT ".$num;
		if($offset==""){
            $sql=$sqlstatement." OFFSET 0";
        }else {
        	$sql=$sqlstatement." OFFSET ". $offset;
        }
        return $this->db->query($sql);
		
	}
	function count_classify_product($classifydesc){
		$this->load->database();
		$sql="SELECT p.product_id FROM product_ads AS p INNER JOIN classify_categories AS c ON p.classify_id = c.classify_id 
		WHERE c.classify_desc LIKE '".pg_escape_string($classifydesc)."'";
		return $this->db->query($sql);
	}
	function get_product_main(){
		$this->load->database();
		$sql="SELECT p.product_id,
            		 p.product_name,
            		 p.product_desc,
            		 p.price,
            		 p.date_post,
            		 p.date_close,
            		 p.classify_id,
            		 p.user_id,
            		 u.user_login,
            		 p.file_path
				FROM product_ads AS p 
				INNER JOIN users AS u ON p.user_id=u.user_id 
		    ORDER BY p.date_post DESC LIMIT 5";
		return $this->db->query($sql);
	}
	
	function get_product_latest(){
		$this->load->database();
		$sql="SELECT p.product_id,
            		 p.product_name,
            		 p.product_desc,
            		 p.price,
            		 p.date_post,
            		 p.date_close,
            		 p.classify_id,
            		 p.user_id,
            		 u.user_login,
            		 p.file_path
				FROM product_ads AS p 
				INNER JOIN users AS u ON p.user_id=u.user_id 
		    ORDER BY p.date_post DESC LIMIT 12";
		return $this->db->query($sql);
	}

	function get_product_user(){
		$this->load->database();
		$Z_USER = Zsession::getUserFromSession();
	    $userid=$Z_USER["user_id"];
		$sql="SELECT p.product_id,
            			 	  p.product_name,
            			 	  p.product_desc,
            				  p.price,
            				  p.date_post,
            				  p.date_close,
            				  p.classify_id,
            				  c.classify_desc,
            				  p.user_id,
            				  u.user_login,
            		 		  p.file_path
            			 FROM product_ads AS p INNER JOIN classify_categories AS c ON p.classify_id = c.classify_id
            			 INNER JOIN users AS u ON p.user_id=u.user_id
            			 WHERE p.user_id=".$userid." 
            			 ORDER BY p.date_post DESC ";
		return $this->db->query($sql);
	}
	
	function select_all_product_ads($num, $offset){
		$this->load->database();
		$sqlstatement="SELECT p.product_id,
            			 	  p.product_name,
            			 	  p.product_desc,
            				  p.price,
            				  p.date_post,
            				  p.date_close,
            				  p.classify_id,
            				  c.classify_desc,
            				  p.user_id,
            				  u.user_login,
            		 		  p.file_path
            			 FROM product_ads AS p INNER JOIN classify_categories AS c ON p.classify_id = c.classify_id 
            			 INNER JOIN users AS u ON p.user_id=u.user_id
            			 ORDER BY p.date_post DESC
          		  	    LIMIT ".$num;
		if($offset==""){
            $sql=$sqlstatement." OFFSET 0";
        }else {
        	$sql=$sqlstatement." OFFSET ". $offset;
        }
        return $this->db->query($sql);
	}
	
	function get_product_detail($productid){
		$this->load->database();
		$sql="SELECT p.product_id,
            		 p.product_name,
            		 p.product_desc,
            		 p.price,
            		 p.contact_name,
            		 p.address,
            		 p.phone,
            		 p.fax,
            		 p.email,
            		 p.date_post,
            		 p.date_close,
            		 p.classify_id,
            		 c.classify_desc,
            		 u.user_tel,
            		 u.date_create,
            		 p.user_id,
            		 u.user_login,
            		 p.file_path,
            		 (SELECT img_name FROM product_ads_imgs imgs WHERE p.product_id = imgs.product_id  LIMIT 1) as img_name
            		 
				FROM product_ads AS p
	      INNER JOIN classify_categories AS c
	      		  ON p.classify_id = c.classify_id 
	      		  INNER JOIN users AS u ON p.user_id=u.user_id
		    WHERE p.product_id=".$productid;
		$query = $this->db->query($sql);
        if ($query->num_rows() <= 0) {
            return null;
        } else {
            return $query->row_array();
        }
	}
	
	function count_ads(){
		$this->load->database();
		$sql="SELECT product_id FROM product_ads";
		return $this->db->query($sql);
	}
	
	function count_ads_by_user($userpost){
		$this->load->database();
		$sql="SELECT product_id FROM product_ads WHERE user_id=".$userpost;
		return $this->db->query($sql);
	}
	
	function save_addnew_ads($proname,$prodesc,$price,$contactname,$address,$phone,$fax,$email,$datepost,$dateclose,$classifyid,$userpost,$filepath){
		$this->load->database();
		$sql="SET TIMEZONE TO 'Asia/Phnom_Penh';
				INSERT INTO product_ads(
            				  product_name,
            				  product_desc,
            				  price,
            				  contact_name,
            				  address,
            				  phone,
            				  fax,
            				  email,
            				  date_post,
            				  date_close,
            				  classify_id,
            				  user_id,
            				  file_path) 
            	  VALUES('".pg_escape_string($proname)."',
		            	  '".pg_escape_string($prodesc)."',
		            	  ".$price.",
		            	  '".pg_escape_string($contactname)."',
		            	  '".pg_escape_string($address)."',
		            	  '".pg_escape_string($phone)."',
		            	  '".pg_escape_string($fax)."',
		            	  '".pg_escape_string($email)."',
		            	  CURRENT_TIMESTAMP,
		            	  '".pg_escape_string($dateclose)."',
		            	  ".$classifyid.",
		            	  ".$userpost.",
						  '".pg_escape_string($filepath)."')";
            $this->db->query($sql);
	}
	
	function save_update_ads($proname,$prodesc,$price,$contactname,$address,$phone,$fax,$email,$dateclose,$classifyid,$proid){
		$this->load->database();
		$sql="SET TIMEZONE TO 'Asia/Phnom_Penh';
			  UPDATE product_ads SET product_name='".pg_escape_string($proname)."',
									 product_desc='".pg_escape_string($prodesc)."',
									 price=".$price.",
									 contact_name='".pg_escape_string($contactname)."',
									 address='".pg_escape_string($address)."',
									 phone='".pg_escape_string($phone)."',
									 fax='".pg_escape_string($fax)."',
									 email= '".pg_escape_string($email)."',
									 date_close='".pg_escape_string($dateclose)."',
									 classify_id=".$classifyid."
							   WHERE product_id=".$proid;
		$this->db->query($sql);
	}
	
	function renew($classify_id){
		$sql="SET TIMEZONE TO 'Asia/Phnom_Penh';
			  UPDATE product_ads SET date_post = CURRENT_TIMESTAMP WHERE product_id=".$classify_id."";
		$this->db->query($sql);
	}
	
	function select_classify_img($productid){
		$this->load->database();
		$sql="SELECT *
				FROM product_ads_imgs WHERE product_id=".$productid;
		return $this->db->query($sql);
	}
	
	function select_first_img($productid){
		$this->load->database();
		$sql="SELECT *
				FROM product_ads_imgs WHERE product_id=".$productid." ORDER BY img_id ASC LIMIT 1";
		$query = $this->db->query($sql);
            if ($query->num_rows() <= 0) {
                return null;
            } else {
            	return $query->row_array();
            }
	}
	
	function select_img_by_id($imgid){
		$this->load->database();
		$sql="SELECT *
				FROM product_ads_imgs WHERE img_id=".$imgid;
		$query = $this->db->query($sql);
            if ($query->num_rows() <= 0) {
                return null;
            } else {
            	return $query->row_array();
            }
	}
	
	function delete_one_img($imgid,$productid){
		$this->load->database();
		$sql="DELETE FROM product_ads_imgs 
					WHERE img_id=".$imgid."
					  AND product_id=".$productid;
		$this->db->query($sql);
	}
	function upload_logo($productid,$imgname){
		$this->load->database();
		$sql="INSERT INTO product_ads_imgs(product_id,img_name) VALUES(".$productid.",'".pg_escape_string($imgname)."')";
		$this->db->query($sql);
	}
	
	function get_product_maxid(){
		$this->load->database();
		$sql="SELECT product_id
            	FROM product_ads ORDER BY product_id DESC LIMIT 1";
			$query = $this->db->query($sql);
            if ($query->num_rows() <= 0) {
                return null;
            } else {
            	return $query->row_array();
            }
	}
	
	// table: hits
	function get_product_hit($productid){
		$this->load->database();
		$sql="SELECT hit_id
				FROM hits WHERE product_id=".$productid;
		return $this->db->query($sql);
	}
	function addnew_product_hit($productid){
		$this->load->database();
		$sql="INSERT INTO hits(product_id) VALUES(".$productid.")";
		$this->db->query($sql);
	}
	function delete_product_hit($productid){
		$this->load->database();
		$sql="DELETE FROM hits
				   WHERE  product_id=".$productid;
		$this->db->query($sql);
	}
	
	
	
	function delete_product_ads($productid){
		$this->load->database();
		$sql="DELETE FROM product_ads
				   WHERE  product_id=".$productid;
		$this->db->query($sql);
	}
	function renew_post($datepost,$dateclose,$productid){
		$this->load->database();
		$sql="UPDATE product_ads SET date_post='".pg_escape_string($datepost)."',
								     date_close='".pg_escape_string($dateclose)."'
							  WHERE  product_id=".$productid;
		$this->db->query($sql);
	}
	
	function get_userpost_by_id($uid){
		$this->load->database();
		$sql="SELECT user_login
				FROM users WHERE user_id=".$uid;
		$query = $this->db->query($sql);
            if ($query->num_rows() <= 0) {
                return null;
            } else {
            	return $query->row_array();
            }
	}
	function count_classify_search($product,$category){
		$this->load->database();
		$sqlstatement="SELECT p.product_id
            			 FROM product_ads AS p INNER JOIN classify_categories AS c ON p.classify_id = c.classify_id 
            			 INNER JOIN users AS u ON p.user_id=u.user_id ";
		if($product=='' && $category=='0'){
			
			$sql=$sqlstatement;
		}
		elseif ($product=='' && $category <> '0'){
			
			$sql=$sqlstatement."WHERE c.classify_id=".$category;
		}
		elseif ($product<>'' && $category == '0'){
			
			$sql=$sqlstatement."WHERE LOWER(p.product_name) LIKE '%".strtolower(pg_escape_string($product))."%'";
		}
		else{//$product<>'' && $category <> '0'
			
			$sql=$sqlstatement."WHERE LOWER(p.product_name) LIKE '%".strtolower(pg_escape_string($product))."%' 
								  AND c.classify_id=".$category;
		}
		return $this->db->query($sql);
	}
	function search_ads($product,$category,$num, $offset){
		$this->load->database();
		$sqlstatement="SELECT p.product_id,
            			 	  p.product_name,
            			 	  p.product_desc,
            				  p.price,
            				  p.date_post,
            				  p.date_close,
            				  p.classify_id,
            				  c.classify_desc,
            				  p.user_id,
            				  u.user_login,
            		 		  p.file_path
            			 FROM product_ads AS p INNER JOIN classify_categories AS c ON p.classify_id = c.classify_id 
            			 INNER JOIN users AS u ON p.user_id=u.user_id ";
		
		if($product==''){
			if($category=='0'){
				if($offset==""){
		            $sql=$sqlstatement."ORDER BY p.product_id DESC
		          		  	    LIMIT ".$num." OFFSET 0";
		        }else {
		        	$sql=$sqlstatement."ORDER BY p.product_id DESC
		          		  	    LIMIT ".$num." OFFSET ". $offset;
		        }
			}else {//$category <> ''
				if($offset==""){
		            $sql=$sqlstatement."WHERE c.classify_id=".$category." ORDER BY p.product_id DESC
		          		  	    LIMIT ".$num." OFFSET 0";
		        }else {
		        	$sql=$sqlstatement."WHERE c.classify_id=".$category." ORDER BY p.product_id DESC
		          		  	    LIMIT ".$num." OFFSET ". $offset;
		        }
			}
			
		}else{//$product<>''
			if ($category == '0'){
				if($offset==""){
		            $sql=$sqlstatement."WHERE LOWER(p.product_name) LIKE '%".strtolower(pg_escape_string($product))."%' ORDER BY p.product_id DESC
		          		  	    LIMIT ".$num." OFFSET 0";
		        }else {
		        	$sql=$sqlstatement."WHERE LOWER(p.product_name) LIKE '%".strtolower(pg_escape_string($product))."%' ORDER BY p.product_id DESC
		          		  	    LIMIT ".$num." OFFSET ". $offset;
		        }
			}else{//$category <> '0'
				if($offset==""){
		            $sql=$sqlstatement."WHERE LOWER(p.product_name) LIKE '%".strtolower(pg_escape_string($product))."%' AND c.classify_id=".$category." ORDER BY p.product_id DESC
		          		  	    LIMIT ".$num." OFFSET 0";
		        }else {
		        	$sql=$sqlstatement."WHERE LOWER(p.product_name) LIKE '%".strtolower(pg_escape_string($product))."%' AND c.classify_id=".$category." ORDER BY p.product_id DESC
		          		  	    LIMIT ".$num." OFFSET ". $offset;
		        }
			}
		}
        return $this->db->query($sql);
	}
	
		function search_count($product,$category){
		$this->load->database();
		$sqlstatement="SELECT p.product_id,
            			 	  p.product_name,
            			 	  p.product_desc,
            				  p.price,
            				  p.date_post,
            				  p.date_close,
            				  p.classify_id,
            				  c.classify_desc,
            				  p.user_id,
            				  u.user_login,
            		 		  p.file_path
            			 FROM product_ads AS p INNER JOIN classify_categories AS c ON p.classify_id = c.classify_id 
            			 INNER JOIN users AS u ON p.user_id=u.user_id ";
		
		if($product==''){
			if($category=='0'){
		            $sql=$sqlstatement."ORDER BY p.product_id DESC";
			}else {//$category <> ''
		            $sql=$sqlstatement."WHERE c.classify_id=".$category." ORDER BY p.product_id DESC";
			}
			
		}else{//$product<>''
			if ($category == '0'){
		            $sql=$sqlstatement."WHERE LOWER(p.product_name) LIKE '%".strtolower(pg_escape_string($product))."%' ORDER BY p.product_id DESC";
			}else{//$category <> '0'
		            $sql=$sqlstatement."WHERE LOWER(p.product_name) LIKE '%".strtolower(pg_escape_string($product))."%' AND c.classify_id=".$category." ORDER BY p.product_id DESC";
			}
		}
        return $this->db->query($sql);
	}
	
	//category view number in main page
	
	function get_view_all_ads(){
		$this->load->database();
		$sql="SELECT product_id FROM product_ads";
		$query = $this->db->query($sql);
		return $query->num_rows();
	}
	
	function get_view_by_cat($cat){
		$this->load->database();
		$sql="SELECT product_id FROM product_ads WHERE classify_id=".$cat;
		$query = $this->db->query($sql);
		return $query->num_rows();
	}
	
	function get_view_properties() {
		$this->load->database();
		$sql = "SELECT property_id FROM property";
		$query = $this->db->query($sql);
		return $query->num_rows();
	}
	
	function get_view_job_wanted() {
		$this->load->database();
		$sql = "SELECT job_wan_id FROM job_wanted";
		$query = $this->db->query($sql);
		return $query->num_rows();
	}
	
	
	function get_view_job_ann() {
		$this->load->database();
		$sql = "SELECT announ_id FROM announcements";
		$query = $this->db->query($sql);
		return $query->num_rows();
	}
	
	
}
?>